package com.wmd.core.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @Author: hy
 * @Description: excel文件处理
 * @Date: 2021/5/24
 */


public class ReadExcelUtils {

    //sheet中总行数
    public int totalRows;

    //每一行总单元格数
    public static int totalCells;

    private Workbook workbook;

    public List<List<String>> readExcelByPath(String filePath){
        List<List<String>> list = new ArrayList<>();
        SimpleDateFormat sdf =   new SimpleDateFormat("yyyy-MM-dd");
        File file = new File(filePath);
        if (!file.exists()){
            return null;
        }
        try {
            if(filePath.endsWith(".xls")){
                workbook = new HSSFWorkbook(POIFSFileSystem.create(file));
            }else if(filePath.endsWith(".xlsx")){
                workbook = new XSSFWorkbook(file);
            }
            Sheet sheet = workbook.getSheetAt(0);
            Row firstRow = sheet.getRow(sheet.getFirstRowNum());
            for (int i=3; i <= sheet.getLastRowNum(); i++){
                Row row = sheet.getRow(i);
                List<String> tempResult = new ArrayList<>();
                if (null != row){
                    for (int j=firstRow.getFirstCellNum(); j< firstRow.getLastCellNum(); j++){
                        String value = "";
                        Cell cell = row.getCell(j);
                        if(null != cell){
                            if (cell.getCellType() == CellType.BOOLEAN){
                                Boolean valueTemp = cell.getBooleanCellValue();
                                value = valueTemp.toString();
                            }else if(cell.getCellType() == CellType.NUMERIC){
                                if(DateUtil.isCellDateFormatted(cell) || DateUtil.isCellDateFormatted(cell)){
                                    Date date = cell.getDateCellValue();
                                    value = sdf.format(date);
                                }else {
                                    DecimalFormat df = new DecimalFormat("#.##");
                                    value = df.format(cell.getNumericCellValue());
                                    String strArr = value.substring(value.lastIndexOf(".")+1);
                                    if(strArr.equals("00")){
                                        value = value.substring(0, value.lastIndexOf("."));
                                    }
                                }
                            }else{
                                value = cell.getStringCellValue();

                            }
                        }
                        tempResult.add(value);
                    }
                }
                list.add(tempResult);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

        return list;
    }


    /**
     * 根据上传的excel进行解析
     * @param file
     * @param totalCells
     * @return
     * @throws IOException
     */
    public List<ArrayList<String>> readExcel(MultipartFile file, int totalCells) throws IOException{
        String fileName = file.getName();
        if (StringUtil.isEmpty(fileName)){
            return null;
        }else {
            String postfix = file.getOriginalFilename();
            postfix = postfix.substring(postfix.lastIndexOf(".") + 1);
            if (!StringUtil.isEmpty(postfix)){
                if ("xls".equalsIgnoreCase(postfix)){
                    return readXls(file, totalCells);
                }else if ("xlsx".equalsIgnoreCase(postfix)){
                    return readXlsx(file, totalCells);
                }else {
                    return null;
                }
            }
        }
        return null;
    }

    /**
     * read the Excel 2010 .xlsx
     * @param file
     * @return
     */
    @SuppressWarnings("deprecation")
    private List<ArrayList<String>> readXlsx(MultipartFile file, int totalCells){
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);
            //读取sheet(页) wb.getNumberOfSheets()
            for(int numSheet=0;numSheet<1;numSheet++){
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
                if(xssfSheet == null){
                    continue;
                }
                totalRows = xssfSheet.getLastRowNum();
                //读取Row,从第4行开始
                for(int rowNum = 3;rowNum <= totalRows;rowNum++){
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if(xssfRow!=null){
                        rowList = new ArrayList<String>();
//                        totalCells = xssfRow.getLastCellNum();
                        //读取列，从第一列开始totalCells+1
                        for(int c=0;c<=totalCells;c++){
                            XSSFCell cell = xssfRow.getCell(c);
                            if(cell==null){
                                rowList.add("");
                                continue;
                            }
                            rowList.add(getXValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * read the Excel 2003-2010 .xls
     * @param file
     * @return
     */
    private List<ArrayList<String>> readXls(MultipartFile file, int totalCells){
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new HSSFWorkbook(input);
            //读取sheet(页) wb.getNumberOfSheets()
            for(int numSheet=0;numSheet<1;numSheet++){
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
                if(hssfSheet == null){
                    continue;
                }
                totalRows = hssfSheet.getLastRowNum();
                //读取Row,从第四行开始
                for(int rowNum = 3;rowNum <= totalRows;rowNum++){
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if(hssfRow!=null){
                        rowList = new ArrayList<String>();
//                        totalCells = hssfRow.getLastCellNum();
                        //读取列，从第一列开始
                        for(short c=0;c<=totalCells;c++){
                            HSSFCell cell = hssfRow.getCell(c);
                            if(cell==null){
                                rowList.add("");
                                continue;
                            }
                            rowList.add(getHValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    /**
     * 单元格格式
     * @param hssfCell
     * @return
     */
    @SuppressWarnings({ "static-access", "deprecation" })
    private static String getHValue(HSSFCell hssfCell){
        SimpleDateFormat sdf =   new SimpleDateFormat("yyyy/MM/dd");
        if (hssfCell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() ==  CellType.NUMERIC) {
            String cellValue = "";
            if(DateUtil.isCellDateFormatted(hssfCell)){
                Date date = DateUtil.getJavaDate(hssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            }else{
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(hssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(".")+1,cellValue.length());
                if(strArr.equals("00")){
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }


    /**
     * 单元格格式
     * @param xssfCell
     * @return
     */
    private static String getXValue(XSSFCell xssfCell){
        SimpleDateFormat sdf =   new SimpleDateFormat("yyyy/MM/dd");
        if (xssfCell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == CellType.NUMERIC) {
            String cellValue = "";
            if(DateUtil.isCellDateFormatted(xssfCell)){
                Date date = DateUtil.getJavaDate(xssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            }else{
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(xssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(".")+1);
                if(strArr.equals("00")){
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }
    /**
     * 自定义xssf日期工具类
     *
     */
/*    static class XSSFDateUtil extends DateUtil {
        protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
            return DateUtil.absoluteDay(cal, use1904windowing);
        }
    }*/

}
